#! /bin/bash

source /etc/profile

if [ -n "$1" ]
then
	do_date=$1
else
	do_date=`date -d "-1 day" +%F`

sql="
with tmp1 as(
select device_id, dt, 
	rank() over(partition by device_id order by dt) rank
from dws.dws_member_start_day
where dt>=date_add('$do_date', -6) and dt<='$do_date'
),
with tmp2 as(
select device_id, date_sub(dt, rank) date_dif
from tmp1
group by device_id, date_dif
having count(*) >= 3
),
with tmp3 as(
select device_id
from tmp2
group by device_id
)
select '$do_date', 
		concat(date_add('$do_date',-6),'_','$do_date'),
		count(*)
from tmp3;
"

hive -e "$sql"